package cn.com.nes.site.module.dao.crm;

import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.stereotype.Repository;

import cn.com.nes.common.util.Page;
import cn.com.nes.site.entity.DataReq;
import cn.com.nes.site.entity.PageData;
import cn.com.nes.site.module.dao.BaseDaoImpl;

@Repository
public class AnalysisDaoImpl extends BaseDaoImpl {

	public PageData getClueStatistics(DataReq req) {
		PageData res = new PageData();
		res.setRetCode(9);
        res.setRetMessage("获取线索分析统计列表失败");
        res.setData(new LinkedHashMap<String, Object>());
		
        try {
        	NamedParameterJdbcTemplate jdbcTemplate = new NamedParameterJdbcTemplate(this.getJdbcTemplate_newg());
        	
        	StringBuffer sql = new StringBuffer();
        	sql.append("SELECT '00' as value, '总线索' as name, COUNT(*) as count FROM clue c where c.isdelete = 0 and c.cluebelongtocompany like concat(:cluebelongtocompany,'%')");
        	sql.append(" UNION ALL ");
        	sql.append("select p.value, p.name, count(sub.uuid) as count from (select c.uuid, c.modifyTime, c.cluestage, c.followupstatus from clue c where c.isdelete = 0 and c.cluebelongtocompany like concat(:cluebelongtocompany,'%')) sub RIGHT JOIN (select tp.* from t_param tp where tp.is_delete = 0 and tp.param_type = '11') p on sub.followupstatus = p.`value` group by p.`name`");
        	sql.append(" UNION ALL ");
        	sql.append("select p.value, p.name, count(sub.uuid) as count from (select c.uuid, c.modifyTime, c.cluestage, c.followupstatus from clue c where c.isdelete = 0 and c.cluebelongtocompany like concat(:cluebelongtocompany,'%')) sub RIGHT JOIN (select tp.* from t_param tp where tp.is_delete = 0 and tp.param_type = '10') p on sub.cluestage = p.`value` where (p.value = '04' or p.value = '05') group by p.`name`");
    		
        	Map<String, Object> queryParams = new HashMap<String, Object>();
    		queryParams.put("cluebelongtocompany", req.getUinfo().getCompanyId());
    		
    		List<Map<String, Object>> allResult = jdbcTemplate.queryForList(sql.toString(), queryParams);
    		
    		sql.setLength(0);
    		sql.append("SELECT '00' as value, '总线索' as name, COUNT(*) as count FROM clue c where c.isdelete = 0 and c.cluebelongtocompany like concat(:cluebelongtocompany,'%') and c.modifyTime > DATE_SUB(NOW(), INTERVAL 3 MONTH)");
    		sql.append(" UNION ALL ");
    		sql.append("select p.value, p.name, count(sub.uuid) as count from (select c.uuid, c.modifyTime, c.cluestage, c.followupstatus from clue c where c.isdelete = 0 and c.cluebelongtocompany like concat(:cluebelongtocompany,'%') and c.modifyTime > DATE_SUB(NOW(), INTERVAL 3 MONTH)) sub RIGHT JOIN (select tp.* from t_param tp where tp.is_delete = 0 and tp.param_type = '11') p on sub.followupstatus = p.`value` group by p.`name`");
    		sql.append(" UNION ALL ");
    		sql.append("select p.value, p.name, count(sub.uuid) as count from (select c.uuid, c.modifyTime, c.cluestage, c.followupstatus from clue c where c.isdelete = 0 and c.cluebelongtocompany like concat(:cluebelongtocompany,'%') and c.modifyTime > DATE_SUB(NOW(), INTERVAL 3 MONTH)) sub RIGHT JOIN (select tp.* from t_param tp where tp.is_delete = 0 and tp.param_type = '10') p on sub.cluestage = p.`value` where (p.value = '04' or p.value = '05') group by p.`name`");
    		
    		List<Map<String, Object>> lastResult = jdbcTemplate.queryForList(sql.toString(), queryParams);
    		
    		res.getData().put("all", allResult);
    		res.getData().put("last", lastResult);
            res.setRetCode(0);
            res.setRetMessage("OK");
        } catch (Exception e) {
			e.printStackTrace();
		} finally {
			
		}
		return res;
	}
	
	public PageData getClueSources(DataReq req) {
		PageData res = new PageData();
		res.setRetCode(9);
        res.setRetMessage("获取流量来源分析数据失败");
		
        try {
        	NamedParameterJdbcTemplate jdbcTemplate = new NamedParameterJdbcTemplate(this.getJdbcTemplate_newg());
        	
        	Map<String, Object> queryParams = new HashMap<String, Object>();
    		queryParams.put("cluebelongtocompany", req.getUinfo().getCompanyId());
    		
    		List<Map<String, Object>> result = jdbcTemplate.queryForList("select '00' `value`,'微信营销' `name`,count(1) count from clue a where a.isdelete=0 and a.cluebelongtocompany like concat(:cluebelongtocompany,'%') and a.cluesource > '05' union SELECT p.value, p.name, count(sub.uuid) as count from (SELECT c.uuid, c.cluesource from clue c where c.isdelete = 0 and c.cluebelongtocompany like concat(:cluebelongtocompany,'%')) sub RIGHT JOIN (select tp.* from t_param tp where tp.is_delete = 0 and tp.param_type = '13' and (tp.companyId='' or tp.companyId is null or tp.companyId='"+req.getUinfo().getCompanyId()+"')) p on sub.cluesource = p.`value` group by p.`value`", queryParams);
    		res.setPage(result);
            res.setRetCode(0);
            res.setRetMessage("OK");
        } catch (Exception e) {
			e.printStackTrace();
		} finally {
			
		}
		return res;
	}
	
	public PageData getClueStagesStatistics(DataReq req) {
		PageData res = new PageData();
		res.setRetCode(9);
        res.setRetMessage("获取获取线索阶段数据失败");
		
        try {
        	NamedParameterJdbcTemplate jdbcTemplate = new NamedParameterJdbcTemplate(this.getJdbcTemplate_newg());
        	
        	Map<String, Object> queryParams = new HashMap<String, Object>();
    		queryParams.put("cluebelongtocompany", req.getUinfo().getCompanyId());
    		
    		List<Map<String, Object>> result = jdbcTemplate.queryForList("select p.value, p.name, count(sub.uuid) as count from (select c.uuid, c.modifyTime, c.cluestage, c.followupstatus from clue c where c.isdelete = 0 and c.cluebelongtocompany like concat(:cluebelongtocompany,'%')) sub RIGHT JOIN (select tp.* from t_param tp where tp.is_delete = 0 and tp.param_type = '10') p on sub.cluestage = p.`value` group by p.`name` order by p.value asc", queryParams);
    		res.setPage(result);
            res.setRetCode(0);
            res.setRetMessage("OK");
        } catch (Exception e) {
			e.printStackTrace();
		} finally {
			
		}
		return res;
	}
	
	public PageData getClueDistributionStatistics(DataReq req) {
		PageData res = new PageData();
		res.setRetCode(9);
        res.setRetMessage("获取获取线索区域分布排行和地图数据失败");
        res.setData(new LinkedHashMap<String, Object>());
		
        try {
        	NamedParameterJdbcTemplate jdbcTemplate = new NamedParameterJdbcTemplate(this.getJdbcTemplate_newg());
        	
        	Map<String, Object> queryParams = new HashMap<String, Object>();
    		queryParams.put("cluebelongtocompany", req.getUinfo().getCompanyId());
    		
    		List<Map<String, Object>> mapDataResult = jdbcTemplate.queryForList("select c.uuid, c.followupstatus, c.poolentrymode, c.cluesource, c.cluename, c.cluephone, c.cluecompany, c.projectname, c.longitude, c.latitude from clue c WHERE c.isdelete = 0 and c.cluebelongtocompany like concat(:cluebelongtocompany,'%') and c.longitude is not null and c.latitude is not null", queryParams);
    		
    		if(req.getPagesize() <= 0) {
    			queryParams.put("size", 10);
    		} else {
    			queryParams.put("size", req.getPagesize());
    		}
    		
    		List<Map<String, Object>> rankingResult = jdbcTemplate.queryForList("SELECT t.xzqhdm, t.xzqhmc, t.xzqhqc, count(t.xzqhdm) count from (select concat(left(c.xzqhdm, 6), '000000') as xzqh, c.* from clue c where c.isdelete = 0 and c.cluebelongtocompany like concat(:cluebelongtocompany,'%')) sub inner join t_xzqh t on sub.xzqh = t.xzqhdm where t.xzqhjb = 3 group by t.xzqhdm order by count desc limit :size", queryParams);
    		
    		res.getData().put("mapData", mapDataResult);
    		res.getData().put("rankingList", rankingResult);
            res.setRetCode(0);
            res.setRetMessage("OK");
        } catch (Exception e) {
			e.printStackTrace();
		} finally {
			
		}
		return res;
	}
	
	public PageData getClueProperiesStatistics(DataReq req) {
		PageData res = new PageData();
		res.setRetCode(9);
        res.setRetMessage("获取获取线索按照时间分布折线图数据失败");
        res.setData(new LinkedHashMap<String, Object>());
		
        try {
        	NamedParameterJdbcTemplate jdbcTemplate = new NamedParameterJdbcTemplate(this.getJdbcTemplate_newg());
        	
        	Map<String, Object> queryParams = new HashMap<String, Object>();
    		queryParams.put("cluebelongtocompany", req.getUinfo().getCompanyId());
    		
    		List<Map<String, Object>> intoPoolWayResult = jdbcTemplate.queryForList("select p.value, p.`name`, count(p.`value`) count from clue c inner join (select tp.* from t_param tp where tp.is_delete = 0 and tp.param_type = '12') p on p.`value` = c.poolentrymode where c.cluebelongtocompany like concat(:cluebelongtocompany,'%') group by p.`value` order by p.`value` asc", queryParams);
    		List<Map<String, Object>> clueTypeSumResult = jdbcTemplate.queryForList("select p.value, p.`name`, count(p.`value`) count from clue c inner join (select tp.* from t_param tp where tp.is_delete = 0 and tp.param_type = '14') p on p.`value` = c.pooltype where c.cluebelongtocompany like concat(:cluebelongtocompany,'%') group by p.`value` order by p.`value` asc", queryParams);
    		List<Map<String, Object>> culeStateResult = jdbcTemplate.queryForList("select p.value, p.`name`, count(p.`value`) count from clue c inner join (select tp.* from t_param tp where tp.is_delete = 0 and tp.param_type = '15') p on p.`value` = c.culestate where c.cluebelongtocompany like concat(:cluebelongtocompany,'%') group by p.`value` order by p.`value` asc", queryParams);
    		
    		res.getData().put("intoPoolWay", intoPoolWayResult);
    		res.getData().put("clueTypeSum", clueTypeSumResult);
    		res.getData().put("culeState", culeStateResult);
            res.setRetCode(0);
            res.setRetMessage("OK");
        } catch (Exception e) {
			e.printStackTrace();
		} finally {
			
		}
		return res;
	}
	
	public PageData getClueStateDateSerialStatistics(DataReq req) {
		PageData res = new PageData();
		res.setRetCode(9);
        res.setRetMessage("获取线索属性统计包括：入池方式、线索类型、线索状态数据失败");
        res.setData(new LinkedHashMap<String, Object>());
		
        try {
        	NamedParameterJdbcTemplate jdbcTemplate = new NamedParameterJdbcTemplate(this.getJdbcTemplate_newg());
        	
        	Map<String, Object> queryParams = new HashMap<String, Object>();
    		queryParams.put("cluebelongtocompany", req.getUinfo().getCompanyId());
    		
    		List<Map<String, Object>> ruchishijianResult = jdbcTemplate.queryForList("select date_format(c.createTime,'%Y年%m月') as time, count(c.createTime) as count from clue c where c.isdelete = 0 and c.cluebelongtocompany like concat(:cluebelongtocompany,'%') and c.cluestage != '25' and c.createTime > DATE_SUB(NOW(), INTERVAL 12 MONTH) group by time order by time", queryParams);
    		List<Map<String, Object>> zhuanshangjiSumResult = jdbcTemplate.queryForList("select date_format(c.modifyTime,'%Y年%m月') as time, count(c.modifyTime) as count from clue c where c.isdelete = 0 and c.cluebelongtocompany like concat(:cluebelongtocompany,'%') and c.cluestage = '25' and c.modifyTime > DATE_SUB(NOW(), INTERVAL 12 MONTH) group by time order by time", queryParams);
    		
    		res.getData().put("入池", ruchishijianResult);
    		res.getData().put("转商机", zhuanshangjiSumResult);
            res.setRetCode(0);
            res.setRetMessage("OK");
        } catch (Exception e) {
			e.printStackTrace();
		} finally {
			
		}
		return res;
	}
	
	public PageData getClueReactionStatistics(DataReq req) {
		PageData res = new PageData();
		res.setRetCode(9);
        res.setRetMessage("获取线索响应统计数据失败");
        res.setData(new LinkedHashMap<String, Object>());
		
        try {
        	NamedParameterJdbcTemplate jdbcTemplate = new NamedParameterJdbcTemplate(this.getJdbcTemplate_newg());
        	
        	Map<String, Object> queryParams = new HashMap<String, Object>();
    		queryParams.put("cluebelongtocompany", req.getUinfo().getCompanyId());
    		
    		StringBuilder sql = new StringBuilder();
    		sql.append("select '2小时内' name, count(*) count from clue c inner join (select lo.clueId, MIN(createTime) firstOperatingTime from log_operate lo where type = '01' group by clueId) log on c.uuid = log.clueId where c.isdelete = 0 and c.cluebelongtocompany like concat(:cluebelongtocompany,'%') and log.firstOperatingTime > DATE_SUB(NOW(), INTERVAL 2 HOUR)");
    		sql.append(" union all ");
    		sql.append("select '24小时内' name, count(*) count from clue c inner join (select lo.clueId, MIN(createTime) firstOperatingTime from log_operate lo where type = '01' group by clueId) log on c.uuid = log.clueId where c.isdelete = 0 and c.cluebelongtocompany like concat(:cluebelongtocompany,'%') and firstOperatingTime > DATE_SUB(NOW(), INTERVAL 24 HOUR) and firstOperatingTime <= DATE_SUB(NOW(), INTERVAL 2 HOUR)");
    		sql.append(" union all ");
    		sql.append("select '三天内' name, count(*) count from clue c inner join (select lo.clueId, MIN(createTime) firstOperatingTime from log_operate lo where type = '01' group by clueId) log on c.uuid = log.clueId where c.isdelete = 0 and c.cluebelongtocompany like concat(:cluebelongtocompany,'%') and firstOperatingTime > DATE_SUB(NOW(), INTERVAL 3 DAY) and firstOperatingTime <= DATE_SUB(NOW(), INTERVAL 24 HOUR)");
    		sql.append(" union all ");
    		sql.append("select '三天以上' name, count(*) count from clue c inner join (select lo.clueId, MIN(createTime) firstOperatingTime from log_operate lo where type = '01' group by clueId) log on c.uuid = log.clueId where c.isdelete = 0 and c.cluebelongtocompany like concat(:cluebelongtocompany,'%') and firstOperatingTime <= DATE_SUB(NOW(), INTERVAL 3 DAY);");
    		
    		List<Map<String, Object>> responseSpeedResult = jdbcTemplate.queryForList(sql.toString(), queryParams);
    		
    		sql.setLength(0);
    		sql.append("select '1个月内' name, count(*) count from clue c where c.isdelete = 0 and c.cluebelongtocompany like concat(:cluebelongtocompany,'%') and c.cluestage = '04' and c.modifyTime > DATE_SUB(NOW(), INTERVAL 1 MONTH)");
    		sql.append(" union all ");
    		sql.append("select '2个月内' name, count(*) count from clue c where c.isdelete = 0 and c.cluebelongtocompany like concat(:cluebelongtocompany,'%') and c.cluestage = '04' and c.modifyTime > DATE_SUB(NOW(), INTERVAL 2 MONTH) and c.modifyTime <= DATE_SUB(NOW(), INTERVAL 1 MONTH)");
    		sql.append(" union all ");
    		sql.append("select '5个月内' name, count(*) count from clue c where c.isdelete = 0 and c.cluebelongtocompany like concat(:cluebelongtocompany,'%') and c.cluestage = '04' and  c.modifyTime > DATE_SUB(NOW(), INTERVAL 5 MONTH) and c.modifyTime <= DATE_SUB(NOW(), INTERVAL 2 MONTH)");
    		sql.append(" union all ");
    		sql.append("select '5个月以上' name, count(*) count from clue c where c.isdelete = 0 and c.cluebelongtocompany like concat(:cluebelongtocompany,'%') and c.cluestage = '04' and c.modifyTime <= DATE_SUB(NOW(), INTERVAL 5 MONTH)");
    		
    		List<Map<String, Object>> toOpportunityResult = jdbcTemplate.queryForList(sql.toString(), queryParams);
    		
    		res.getData().put("responseSpeed", responseSpeedResult);
    		res.getData().put("toOpportunity", toOpportunityResult);
            res.setRetCode(0);
            res.setRetMessage("OK");
        } catch (Exception e) {
			e.printStackTrace();
		} finally {
			
		}
		return res;
	}
	
	public PageData getEmployeeFollowUpList(DataReq req) {
		PageData res = new PageData();
		res.setRetCode(9);
        res.setRetMessage("获取最近三个月的员工线索跟进信息列表失败");
        res.setData(new LinkedHashMap<String, Object>());
		
        try {
        	String cluebelongtocompany = req.getUinfo().getCompanyId();
    		
    		StringBuffer sql = new StringBuffer();
    		//sql.append("SELECT u.id userId, u.nickName, d.departmentName, sum( op.remark = '认领线索' ) claim, count(*) followUp, 0 callNum, sum( op.remark = '商机转移' ) toOpportunity, (select sum(o.opportunitymoney) from opportunity o where o.createUid = u.id) opportunitymoney FROM t_user u INNER JOIN log_operate op ON op.userid = u.id  left JOIN t_department d ON d.departmentId = u.departmentId WHERE u.companyId like concat(?,'%') and op.createTime > DATE_SUB( NOW(), INTERVAL 3 MONTH ) GROUP BY u.id");
    		sql.append(" SELECT	u.id userId,	u.nickName,	d.departmentName, ");
    		sql.append(" (select count(*) from clue m where m.clueowner=u.id and m.createTime > DATE_SUB(NOW(), INTERVAL 3 MONTH)) claim, ");
    		sql.append(" 0 callNum, ");    		sql.append(" (select count(*) from log_claim n where n.createUid=u.id  and n.createTime > DATE_SUB(NOW(), INTERVAL 3 MONTH) and n.type='01') followUp, ");

    		sql.append(" (select count(*) from opportunity s where s.opportunityowner=u.id and s.createTime > DATE_SUB(NOW(), INTERVAL 3 MONTH)) toOpportunity, ");
    		sql.append(" (SELECT	if(sum(o.opportunitymoney) is null,0,sum(o.opportunitymoney))	FROM	opportunity o	WHERE	o.opportunityowner = u.id  and o.createTime > DATE_SUB(NOW(), INTERVAL 3 MONTH)	) opportunitymoney ");
    		sql.append(" FROM	t_user u ");
    		sql.append(" LEFT JOIN t_department d ON d.departmentId = u.departmentId ");
    		sql.append(" WHERE u.sfsc=0 and	u.companyId LIKE concat(?, '%') ");
    		sql.append(" GROUP BY	u.id ");
    		
            Page page = new Page(this.getJdbcTemplate_newg(), " from (" + sql.toString() + ") t", req.getPageid(), req.getPagesize(), res, new Object[]{ cluebelongtocompany }) ;
            sql.append(" limit ").append(page.getOffsetlimit()).append(",").append(req.getPagesize()) ;
            List<Map<String, Object>> list = this.getJdbcTemplate_newg().queryForList(sql.toString(), new Object[]{ cluebelongtocompany });
    		
    		res.setPage(list);
            res.setRetCode(0);
            res.setRetMessage("OK");
        } catch (Exception e) {
			e.printStackTrace();
		} finally {
			
		}
		return res;
	}
	
}
